
#coding=utf-8
from ast import If
from enum import Flag
from tkinter import NO, SW
from typing import Any, Union
from uu import Error
from xmlrpc.client import Boolean
import aiomysql
import re

# 案例
#   
#        
#             sql = {"host": "xxx", "port"=xxx, "user": "xxxx", "password": "xxx", "db"="xxx"}
#             mysql = await init_mysql(host=sql["host"],
#                                  port=sql["port"],
#                                  user=sql["username"],
#                                  password=sql["password"],
#                                  db=sql["database"], debug=True)  
#         
#         res = await mysql.queryList("select * from users where user_id in ( :userId ) and account=:account", {
#             "replacement": {
#                 "userId": [1],
#                 "account": "admin"
#             } 
#         })  
#          >>>(1, 'admin', '123456')

# 参考博客 https://blog.csdn.net/ababab12345/article/details/133252425

async def init_mysql(
        host: str, 
        port: Union[int, str],
        user: str,
        password: str=None,
        db: str=None,
        debug = False
        ):
    mysql = Mysql(
        host=host,
        port=port,
        user=user,
        password=password,
        db=db,
        debug= debug 
    )
    await mysql.connect()
    return mysql


class Obj: 
    replacement: dict=None
    whereStr: str=None

class ReplacementError(Error):
    pass

"""
数据类型字符添加，如字符串增加‘’
"""
def data_type_chart(data):
    if isinstance(data, str)  :
        return f"'{data}'"
    
    return data

def str_to_sql(sql, replacement)->str:
    pattern = r'\(\s*(.*?)\s*\)' # 去掉括号中间的空格
    sql = re.sub(pattern, r'(\1)', sql)
    sqlList = sql.split(" ")
    arr = []
    
    for data in sqlList:
            
        if re.search(r'=:', data):
            # A=:B
            data = data.split("=:")
            key = data[1]

            if (key in replacement)  == False:
                raise Error(f"清检查replacement->{key}")
            
            replacement[key] = data_type_chart(replacement[key])
            data = f'{data[0]}={replacement[key]}' 
        elif re.search(r'^\(\:', data):
            # a IN/in (:)
            key = data.replace('(:', '').replace(")", '')
            value = ''

            if (key in replacement)  == False:
                raise Error(f"清检查replacement->{data}")
            
            for v in replacement[key]:
                v = data_type_chart(v)
                value += f',{v}'

            data = f"({value[1:]})"

        arr.append(data)
            
    sql = ' '.join(arr)
    return sql

class Mysql:

    client: Any = None

    def __init__(
        self, 
        host: str, 
        port: Union[int, str],
        user: str,
        password: str=None,
        db: str=None,
        debug = False
    ):
        self.host = host
        self.port = port
        self.user = user
        self.password = password
        self.db = db
        self.host = host
        self.host = host
        self.debug = debug
        
    # 连接数据库
    async def connect(self):
        try:
            conn = await aiomysql.connect(host=self.host, port=self.port, user=self.user, password=self.password, db=self.db)
            self.client = await conn.cursor()
        except Error as err:
        # except aiomysql.OperationalError as err:
            # 连接失败
            print(f"\033[1;31m连接mysql（ip:'{self.host}',db:'{self.db}'）失败，ERROR: {err}\033[0m")
            raise aiomysql.OperationalError("连接失败")

    # 单个查询
    async def query(self, sql: str=None, obj: Obj=None):
        if str == None or len(sql) == 0:
             
            return None
        replacement = None
        if obj and obj["replacement"]:
            replacement =  obj["replacement"]
         
        # 带替换标签，但没有给替换值
        if re.search(r"\s*:\s*", sql) and replacement == None:
            raise Error("缺少replacement")
        
        sql = str_to_sql(sql, replacement)

        if obj["whereStr"]:
             sql += str_to_sql(obj["whereStr"], replacement)

        if self.debug:
            print(sql)

        await self.client.execute(sql)

        return await self.client.fetchone()
    
    # 多个查询
    async def queryList(self, sql: str=None, obj: Obj=None):
        if str == None or len(sql) == 0:
             
            return None
        replacement = None
        if obj and obj["replacement"]:
            replacement =  obj["replacement"]
         
        # 带替换标签，但没有给替换值
        if re.search(r"\s*:\s*", sql) and replacement == None:
            raise Error("缺少replacement")
        
        sql = str_to_sql(sql, replacement)

        if self.debug:
            print(sql)

        await self.client.execute(sql)

        return await self.client.fetchall()